package org.example

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}

object SQL3 {
  def main(args: Array[String]): Unit = {
    // 创建spark运行环境
    val spark = SparkSession
      .builder()
      .master("local[*]")
      .appName("spark")
      .getOrCreate()
    val sc = spark.sparkContext
    // 表结构读取电影用户数据
    val schemaUser = StructType(Seq(
      StructField("id",IntegerType,nullable = false),
      StructField("gender",StringType),
      StructField("age",IntegerType),
      StructField("occupation",IntegerType),
      StructField("location",StringType)
    ))
    val user = spark.read.option("sep","::").schema(schemaUser)
      .csv("src/main/resources/users.dat")
     user.show(5)

    // 表结构读取评分数据
    val schemaRating = StructType(Seq(
      StructField("userId", IntegerType, nullable = false),
      StructField("movieId", IntegerType, nullable = false),
      StructField("rating", IntegerType),
      StructField("timestamp", StringType)
    ))
    val rating = spark.read.option("sep", "::").schema(schemaRating)
      .csv("src/main/resources/ratings.dat")
    // 表结构读取电影数据
    val schemaMovie = StructType(Seq(
      StructField("movieId", IntegerType, nullable = false),
      StructField("title", StringType),
      StructField("genres", StringType)
    ))
    val movie = spark.read.option("sep", "::").schema(schemaMovie)
      .csv("src/main/resources/movies.dat")
    // 查询18岁女生评分为5分的所有电影名称
    val result = user
      .filter("gender='F' and age=18") // 筛选18岁女生
      .join(rating, user("id") === rating("userId")) // 用户表和评分表连接
      .filter("rating=5") // 筛选评分5分
      .join(movie, rating("movieId") === movie("movieId")) // 评分表和电影表连接
      .select("title") // 选择电影名称
      .distinct() // 去重
      .show(truncate = false)

   /* // 1.查询 替换 where/filter
    user.where("gender='F' and age =18").show(3)
    user.filter("gender='F' and age =18").show(3)

    spark.udf.register("replace",(x:String) => {
      x match {
        case "M" => 0
        case "F" => 1
      }
    })
    user.selectExpr("id","replace(gender)","age").show(3)
    user.select("id","age","location").show(3)

    // 2.排序  orderBy  sort
    user.orderBy(desc("age")).show(5)
    user.sort(-user("id")).show(5)
    // 3.分组
    user.groupBy("gender").count().show()
    // 4.连接 join(DataFrame,"列名","连接方式left_outer")
*/
    // 5.求18岁女生评分电影为5分的所有电影名

    sc.stop()
  }

}
